# -*- coding: utf-8 -*-
import duckdb
import os
from utils.path_utils import get_project_root
from config import ini_op


databasetype = ini_op.getinivalue('basic_config', 'databasetype')[0]
def get_databasetype():
    global databasetype
    databasetype = ini_op.getinivalue('basic_config', 'databasetype')[0]

# 连接到 DuckDB 数据库
def get_connection():
    if databasetype == 'GS':
        return duckdb.connect(os.path.join(get_project_root(), 'database', 'duckdb', 'szy_gs.db'))
    else:
        return duckdb.connect(os.path.join(get_project_root(), 'database', 'duckdb', 'szy_u8.db'))
## 注释

# 关闭自动提交，开始一个显式的事务
# conn.execute("BEGIN TRANSACTION")

# 执行多个操作
# conn.execute("INSERT INTO XXXX")
# conn.execute("UPDATE my_table SET XXX")

# 提交事务
# conn.execute(#"COMMIT")

# 如果有错误，可以回滚事务
# conn.execute("ROLLBACK")

# 关闭连接
# conn.close()


def execute(sql):
    conn = get_connection()
    conn.execute(sql)
    conn.close()
    return "SQL executed"


def query(sql):
    conn = get_connection()

    result = conn.execute(sql).df()
    conn.close()
    return result

def show_tables():
    conn = get_connection()
    # tables = conn.execute("SHOW TABLES").fetchall()
    tables = conn.execute("select table_name from information_schema.tables").fetchall()
    conn.close()
    return tables

def table(name):
    conn = get_connection()
    columns = conn.execute(f"DESCRIBE {name}").fetchall()
    data = conn.execute(f"SELECT * FROM {name}").df()
    conn.close()
    return [columns, data]


def init_database_metadata():
    '''
    初始化数据库对象
    '''
    sqlfile = os.path.join(get_project_root(), "sql", "duckdb_accounting_init.sql")
    # print(sqlfile)
    with open(sqlfile, 'r', encoding='utf-8') as file:
        sql_script = file.read()
    # 读取配置文件
    data = ini_op.getjson('basic_config')
    # 替换SQL中的占位符
    sql_script = (sql_script.replace("$p_dwbh", data.get('dwbh'))
                  .replace("$p_dwid", data.get("dwid"))
                  .replace("$p_ledger", data.get("ledger"))
                  .replace("$p_chartofacc", data.get("kmtx_id"))
                  )
    # print(sql_script)
    return execute(sql_script)
def init_database_data():
    '''
    初始化数据库数据
    '''
    if databasetype == 'GS':
        sqlfile = os.path.join(get_project_root(), "sql", "init_gs.sql")
    else:
        sqlfile = os.path.join(get_project_root(), "sql", "init_u8.sql")
    # print(sqlfile)
    with open(sqlfile, 'r', encoding='utf-8') as file:
        sql_script = file.read()
    # 读取配置文件
    data = ini_op.getjson('basic_config')
    # 替换SQL中的占位符
    sql_script = (sql_script.replace("$p_dwbh", data.get('dwbh'))
                  .replace("$p_dwid", data.get("dwid"))
                  .replace("$p_ledger", data.get("ledger"))
                  .replace("$p_chartofacc", data.get("kmtx_id"))
                  )
    # print(sql_script)
    return execute(sql_script)


def querytable(tablename):
    '''
    测试方法
    测试内存数据库
    '''
    # to start an in-memory database, 不同会话中不可访问, 不进行持久化
    con = duckdb.connect(database=":memory:")
    # create a table
    con.execute(f"CREATE TABLE {tablename} (item VARCHAR, value DECIMAL(10, 2), count INTEGER)")
    # insert two items into the table
    con.execute(f"INSERT INTO {tablename} VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)")

    # retrieve the items again
    data = con.execute(f"SELECT * FROM {tablename}").df()
    # print(con.fetchall())

    return data.to_html()

def test(p_dwbh, p_model, p_kjnd, p_fybh):
    '''
    测试方法
    '''
    con = get_connection()
    sql = f"""
        select count(uuid) from NW_FYMC_MX fy
           where fy.dwbh = '{p_dwbh}'
             and (fy.kjnd is null or '{p_kjnd}' is null or fy.kjnd = '{p_kjnd}')  -- 年度
             and fy.fybh = '{p_fybh}'
             and fy.model = '{p_model}'
             and fy.status = '1'
             and fy.jsfs in ('JFFS','DFFS')
        """
    print(sql)
    print(con.execute(sql).fetchall())
    print(con.execute(sql).fetchone())
    print(con.execute(sql).fetchone()[0])
    jd_count = con.execute(sql).fetchone()[0]
    print(type(jd_count))

    p_kjnd = None
    sql = f"""
        select count(uuid) from NW_FYMC_MX fy
           where fy.dwbh = $p_dwbh
             and (fy.kjnd is null or $p_kjnd is null or fy.kjnd = $p_kjnd )  -- 年度
             and fy.fybh = $p_fybh
             and fy.model = $p_model
             and fy.status = '1'
             and fy.jsfs in ('JFFS','DFFS')
        """
    print(sql)
    print(con.execute(sql, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_kjnd': p_kjnd, 'p_fybh': p_fybh}).fetchone()[0])

    return jd_count

